/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2008                    */
/* Created on:     10/03/2014 11:43:36                          */
/*==============================================================*/


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CONTROL') and o.name = 'FK_CONTROL_REFERENCE_FUNCIONA')
alter table CONTROL
   drop constraint FK_CONTROL_REFERENCE_FUNCIONA
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CONTROL') and o.name = 'FK_CONTROL_REFERENCE_EXAMEN')
alter table CONTROL
   drop constraint FK_CONTROL_REFERENCE_EXAMEN
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('FUNCIONARIO') and o.name = 'FK_FUNCIONA_REFERENCE_GRUPO')
alter table FUNCIONARIO
   drop constraint FK_FUNCIONA_REFERENCE_GRUPO
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CONTROL')
            and   type = 'U')
   drop table CONTROL
go

if exists (select 1
            from  sysobjects
           where  id = object_id('EXAMEN')
            and   type = 'U')
   drop table EXAMEN
go

if exists (select 1
            from  sysobjects
           where  id = object_id('FUNCIONARIO')
            and   type = 'U')
   drop table FUNCIONARIO
go

if exists (select 1
            from  sysobjects
           where  id = object_id('GRUPO')
            and   type = 'U')
   drop table GRUPO
go

if exists (select 1
            from  sysobjects
           where  id = object_id('PLANIFICACION')
            and   type = 'U')
   drop table PLANIFICACION
go

/*==============================================================*/
/* Table: CONTROL                                               */
/*==============================================================*/
create table CONTROL (
   FUN_RUT              varchar(10)          null,
   EX_ID                int                  null,
   CON_FECHA            datetime             null
)
go

/*==============================================================*/
/* Table: EXAMEN                                                */
/*==============================================================*/
create table EXAMEN (
   EX_ID                int                  identity,
   EX_DESCRIPCION       varchar(50)          null,
   constraint PK_EXAMEN primary key (EX_ID)
)
go

/*==============================================================*/
/* Table: FUNCIONARIO                                           */
/*==============================================================*/
create table FUNCIONARIO (
   FUN_RUT              varchar(10)          not null,
   FUN_NOMBRE           varchar(50)          not null,
   GRUPO_ID             int                  null,
   FUN_GRADO            varchar(30)          null,
   FUN_UNIDAD           varchar(30)          null,
   FUN_EDAD             int                  null,
   FUN_PESO             decimal(10,2)        null,
   FUN_ESTATURA         decimal(10,2)        null,
   FUN_IMC              decimal(10,2)        null,
   FUN_TIPO             int                  null,
   constraint PK_FUNCIONARIO primary key nonclustered (FUN_RUT)
)
go

/*==============================================================*/
/* Table: GRUPO                                                 */
/*==============================================================*/
create table GRUPO (
   GRUPO_ID             int                  identity,
   GRUPO_NOMBRE         varchar(50)          null,
   constraint PK_GRUPO primary key (GRUPO_ID)
)
go

/*==============================================================*/
/* Table: PLANIFICACION                                         */
/*==============================================================*/
create table PLANIFICACION (
   PLAN_ID              varchar(11)          not null,
   PLAN_CLASE           int                  null,
   PLAN_CURSO           varchar(30)          null,
   PLAN_UNI_APRENDI     varchar(50)          null,
   PLAN_INDICADORES     varchar(50)          null,
   PLAN_APRENDI_ESPERADO varchar(50)          null,
   PLAN_TEMA_CONTENIDO  varchar(100)         null,
   PLAN_ACTIV_DESARROLLO varchar(400)         null,
   PLAN_ACTIV_TERMINO   varchar(100)         null,
   PLAN_PROCED_EVAL     varchar(100)         null,
   PLAN_BIBLIOGRAFIA    varchar(100)         null,
   PLAN_OBSERVACIONES   varchar(200)         null,
   constraint PK_PLANIFICACION primary key (PLAN_ID)
)
go

alter table CONTROL
   add constraint FK_CONTROL_REFERENCE_FUNCIONA foreign key (FUN_RUT)
      references FUNCIONARIO (FUN_RUT)
go

alter table CONTROL
   add constraint FK_CONTROL_REFERENCE_EXAMEN foreign key (EX_ID)
      references EXAMEN (EX_ID)
go

alter table FUNCIONARIO
   add constraint FK_FUNCIONA_REFERENCE_GRUPO foreign key (GRUPO_ID)
      references GRUPO (GRUPO_ID)
go

